import threading
import time

import pymysql
import pandas as pd
from src import EnvironmentVariables
import subprocess


class ArticlesManager:
    __host = "localhost"
    __user = "root"
    __password = "lyx021017"
    __database = "FINANCIAL_REPORTS"
    __db_lock = threading.Lock()

    def __init__(self):
        try:
            self.__db = pymysql.connect(host=ArticlesManager.__host, user=ArticlesManager.__user,
                                        password=ArticlesManager.__password,
                                        database=ArticlesManager.__database)

            self.__cursor = self.__db.cursor()
            sql_creat_table = """CREATE TABLE IF NOT EXISTS `articles`( 
                                `article_id` INT UNSIGNED AUTO_INCREMENT, 
                                `title` VARCHAR(200) NOT NULL, 
                                `author` VARCHAR(100) , 
                                `post_date` DATE, 
                                `link` VARCHAR(500) NOT NULL, 
                                `main_body` TEXT, 
                                UNIQUE(link),
                                PRIMARY KEY (article_id)
                            )ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
            self.__cursor.execute(sql_creat_table)

        except Exception as e:
            print(e)
            print('Fail to connect to MySQL')
            raise e

    def add_article(self, _article: dict):
        elements_name = ['title', 'author', 'post_date', 'link', 'main_body']
        article = []
        for ele in elements_name:
            if ele in _article.keys() and _article[ele] is not None:
                article.append(f"\'{pymysql.converters.escape_string(_article[ele])}\'")
            else:
                if ele == 'link':
                    raise Exception()
                article.append("NULL")
        article = ','.join(article)
        sql_insert = f'REPLACE INTO articles (title,author,post_date,link,main_body) VALUES ({article});'

        # print(sql_insert)
        with ArticlesManager.__db_lock:
            self.__cursor.execute(sql_insert)
            self.__db.commit()

    def del_article(self, article_id):
        sql_del = f'DELETE FROM articles WHERE article_id={article_id};'
        with ArticlesManager.__db_lock:
            self.__cursor.execute(sql_del)
            self.__db.commit()

    def update(self, **kwargs):
        # print(kwargs)
        article_id = kwargs['article_id']
        new_data = ''
        for i, (key, value) in enumerate(kwargs.items()):
            if key == 'article_id':
                continue
            if i != 0:
                new_data += ', '
            new_data += f"{key}=\'{pymysql.converters.escape_string(value)}\' "
        sql_update = f'UPDATE articles SET {new_data} WHERE article_id={article_id}'
        with ArticlesManager.__db_lock:
            self.__cursor.execute(sql_update)
            self.__db.commit()
            # print(f'update {article_id}')

    def update4df(self, df: pd.DataFrame, **kwargs):
        with ArticlesManager.__db_lock:
            update_col = kwargs['update_col'] if "update_col" in kwargs.keys() else df.columns.tolist()
            assert "article_id" in df.columns.tolist()
            for index, row in df.iterrows():
                new_data = ""
                article_id = row["article_id"]
                for i, key in enumerate(update_col):
                    if key == 'article_id':
                        continue
                    if i != 0:
                        new_data += ", "
                    new_data += f"{key}=\'{pymysql.converters.escape_string(str(row[key]))}\' "
                sql_update = f'UPDATE articles SET {new_data} WHERE article_id={article_id}'
                self.__cursor.execute(sql_update)
            self.__db.commit()

    def get_data(self, **kwargs):
        """
        :param kwargs:
            clause 条件,
            column_name 查找属性名,
            need_pd_df 是否返回 pandas.DataFrame,
            limit 数据数量，或偏置,
            order_by 排序参数
        :return:
        """
        clause = 'WHERE ' + kwargs['clause'] if 'clause' in kwargs.keys() else ""
        need_column = ','.join(kwargs['column_name']) if 'column_name' in kwargs.keys() else "*"
        return_pd_df = kwargs['need_pd_df'] if 'need_pd_df' in kwargs else False
        out_num = 'LIMIT ' + str(kwargs['limit']) if 'limit' in kwargs.keys() else ""
        order = 'ORDER BY ' + kwargs['order_by'] if 'order_by' in kwargs.keys() else ""
        sql_select = f'SELECT {need_column} FROM articles {clause} {order} {out_num};'
        with ArticlesManager.__db_lock:
            # print(sql_select)
            self.__cursor.execute(sql_select)
            results = self.__cursor.fetchall()
            # print(results)
            if return_pd_df:
                assert 'column_name' in kwargs.keys()
                return pd.DataFrame(results, columns=kwargs['column_name'])
            return results

    def dump2excel(self):
        sql = "SELECT article_id, title, author, link FROM articles WHERE LENGTH(main_body)<500 LIMIT 20000;"
        self.__cursor.execute(sql)
        self.__cursor.execute(sql)
        res = self.__cursor.fetchall()
        dataframe = pd.DataFrame(res, columns=["article_id", "title", "author", "link"])
        dataframe.set_index("article_id")
        # dataframe = dataframe.drop(columns='index')
        dataframe.to_excel("data.xlsx")
        return res

    def re_connect(self):
        self.__db = pymysql.connect(host=ArticlesManager.__host, user=ArticlesManager.__user,
                                    password=ArticlesManager.__password,
                                    database=ArticlesManager.__database)

        self.__cursor = self.__db.cursor()


def cold_back_up():
    p = subprocess.Popen(EnvironmentVariables.COLD_BACKUP_BAT, shell=True)
    p.wait()
    time.sleep(10)
    article_manager.re_connect()


article_manager = ArticlesManager()

if __name__ == '__main__':
    cold_back_up()
